package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.util.List;

import static com.hualude.roadsign.service.impl.CountServiceImpl.convertParams;

/**
 * Created by anqian on 2017/4/7.
 */
public class CountRepositoryImpl {
    @PersistenceContext
    private EntityManager em;


    public Integer getCount4DT(String timeLimit, String installType, String signFixed, String signType, String orgName, Integer actor, String orgIds) throws Exception {
        String hql = "select count(*) from tb_roadsign where RoadSignId like '%'";
        if (!timeLimit.equals("-1")) {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and roadSignInstallTime between '" + dateStr[0] + "' and '" + dateStr[1] + "' ";
        }
        if (!installType.equals("-1")) {
            hql += " and RoadSignInstallType = " + installType;
        }
        if (!signFixed.equals("-1")) {
            hql += " and RoadSignFixed = " + signFixed;
        }
        if (!signType.equals("-1")) {
            hql += " and RoadSignType = " + signType;
        }
        if (!signType.equals("-1")) {
            hql += " and RoadSignType = " + signType;
        }
        if (!orgName.equals("-1")) {
            hql += " and OrgId = " + orgName;
        }
        if (actor != 1) {
            hql += " and (OrgId IN (" + orgIds + ") or " +
                    "MainOrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<RoadsignDto> getRoadSign4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        String installType = sQuery[1];
        String signFixed = sQuery[2];
        String signType = sQuery[3];
        String orgName = sQuery[4];
        String hql = "select r.RoadSignId,r.RoadSignDirection,r.RoadSignType,r.RoadSignPic,r.RoadSignInstallType,r.RoadSignFixed," +
                "r.RoadSignSecurity,r.RoadSignBug,r.roadSignInstallTime,o.orgName from tb_roadsign r LEFT JOIN tb_org o on r.OrgId = o.OrgId where r.RoadSignId LIKE '%'";
        if (!timeLimit.equals("-1")) {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and r.roadSignInstallTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!installType.equals("-1")) {
            hql += " and r.RoadSignInstallType = " + installType;
        }
        if (!signFixed.equals("-1")) {
            hql += " and r.RoadSignFixed = " + signFixed;
        }
        if (!signType.equals("-1")) {
            hql += " and r.RoadSignType = " + signType;
        }
        if (!orgName.equals("-1")) {
            hql += " and r.OrgId = " + orgName;
        }
        if (actor != 1) {
            hql += " and (r.OrgId IN (" +
                    orgIds + ") or r.MainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER by r.RoadSignInstallTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadsignDto.class));
        List<RoadsignDto> roadsignDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadsignDto;
    }

    public Integer getRoadPoleCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select count(*) from tb_roadpole where RoadPoleId like '%'";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:道路 4:起始路段 5:终止路段 6:主管单位 7:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and Province = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and City = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and Area = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and RoadName ='" + sQuery[3] + "'";
        }
        if (!sQuery[4].equals("-1") && sQuery[5].equals("-1")) {
            hql += " and (StartRoad ='" + sQuery[4] + "' or EndRoad ='" + sQuery[4] + "')";
        }
        if (sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and (EndRoad ='" + sQuery[5] + "' or StartRoad ='" + sQuery[5] + "')";
        }
        if (!sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and ((StartRoad ='" + sQuery[4] + "' and EndRoad ='" + sQuery[5] + "') or" +
                    "(StartRoad = '" + sQuery[5] + "' and EndRoad = '" + sQuery[4] + "'))";
        }
        if (!sQuery[6].equals("-1")) {
            hql += " and MainOrgId = '" + sQuery[6] + "'";
        }
        if (!sQuery[7].equals("-1")) {
            hql += " and OrgId = '" + sQuery[7] + "'";
        }
        if (actor != 1) {
            hql += " and (OrgId IN (" + orgIds + ") or " +
                    "MainOrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<RoadpoleDto> getRoadPole4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select r.RoadPoleId,r.RoadPolePic,r.Province,r.City,r.Area,r.RoadName,m.OrgName as mainOrgName,o.OrgName as orgName,r.Longitude," +
                "r.Latitude,r.RoadPoleBridge,r.RoadPoleIsComplete,r.RoadPoleSecurity,r.RoadPoleCreateTime,r.StartRoad,r.EndRoad from tb_roadpole r " +
                "LEFT JOIN tb_org m ON r.MainOrgId = m.OrgId " +
                "LEFT JOIN tb_org o ON r.OrgId = o.OrgId " +
                "where r.RoadPoleId LIKE '%'";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:道路 4:起始路段 5:终止路段 6:主管单位 7:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and r.Province = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and r.City = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and r.Area = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and r.RoadName ='" + sQuery[3] + "'";
        }
        if (!sQuery[4].equals("-1") && sQuery[5].equals("-1")) {
            hql += " and (r.StartRoad ='" + sQuery[4] + "' or r.EndRoad ='" + sQuery[4] + "')";
        }
        if (sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and (r.EndRoad ='" + sQuery[5] + "' or r.StartRoad ='" + sQuery[5] + "')";
        }
        if (!sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and ((r.StartRoad ='" + sQuery[4] + "' and r.EndRoad ='" + sQuery[5] + "') or" +
                    "(r.StartRoad = '" + sQuery[5] + "' and r.EndRoad = '" + sQuery[4] + "'))";
        }
        if (!sQuery[6].equals("-1")) {
            hql += " and r.MainOrgId = '" + sQuery[6] + "'";
        }
        if (!sQuery[7].equals("-1")) {
            hql += " and r.OrgId = '" + sQuery[7] + "'";
        }
        if (actor != 1) {
            hql += " and (r.OrgId IN (" + orgIds + ") or " +
                    "r.MainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY r.RoadPoleCreateTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        List<RoadpoleDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public Integer getFaultCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select count(*) from tb_signfault f,tb_org o where f.OrgId = o.OrgId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and f.SignFaultTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (f.OrgId IN (" + orgIds + ") or " +
                    "f.MainOrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<SignfaultDto> getFault4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select f.signFaultId,f.RoadSignId,f.SignFaultPic,f.SignFaultLevel,f.CompanyCheck,f.MainCheck," +
                "f.SignFaultTime,f.SignCheckTime,o.OrgName,p.RoadPoleId,p.RoadName,p.EndRoad,p.StartRoad,p.Area,p.City,p.Province" +
                ",p.RoadPoleShape,u.UserName,p.RoadPoleSize,p.RoadPolePic,s.RoadSignPic,o2.orgName as mainOrgName" +
                " from tb_signfault f,tb_org o,tb_org o2,tb_roadpole p,tb_user u,tb_roadsign s " +
                "where f.OrgId = o.OrgId and p.mainOrgId=o2.orgId and f.RoadSignId = s.RoadSignId AND s.RoadPoleId = p.RoadPoleId AND " +
                "s.CreateUserId = u.UserId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and f.SignFaultTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (f.OrgId IN (" + orgIds + ") or " +
                    "f.MainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY f.SignFaultTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignfaultDto.class));
        List<SignfaultDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public List<MaintenanceDto> getMaintenance4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "select m.MaintenanceId,m.SignFaultPic,m.MaintenancePic,m.MaintenanceSignStatus,m.MaintenanceTime," +
                "o.OrgName,m.RoadSignId,p.RoadPoleId,p.RoadName,p.EndRoad,p.StartRoad,p.Area,p.City,p.Province," +
                "p.RoadPoleShape,u.UserName,p.RoadPoleSize,p.RoadPolePic,s.RoadSignPic ,o2.orgName as mainOrgName " +
                "from tb_maintenance m,tb_user u,tb_org o,tb_org o2, tb_roadpole p,tb_roadsign s where" +
                " m.MaintenanceStatus=2 and m.maintenancePeopleId = u.userId and p.mainOrgId=o2.orgId " +
                " and u.OrgId = o.OrgId and m.RoadSignId = s.RoadSignId and s.RoadPoleId = p.RoadPoleId " +
                "and p.CreateUserId = u.UserId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and m.MaintenanceTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (u.OrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY m.MaintenanceTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public Integer getMaintenanceCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "select count(*) from tb_maintenance m,tb_user u,tb_org o where m.MaintenanceStatus=2 and" +
                " m.maintenancePeopleId = u.userId AND u.OrgId = o.OrgId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and MaintenanceTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (u.orgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public String getactorIds(Integer userId) throws Exception {
        String hql = "select GROUP_CONCAT(actorId) from tb_user_actor where userId =" + userId;
        Query query = em.createNativeQuery(hql);
        String actorIds = query.getSingleResult().toString();
        return actorIds;
    }

    public String getChildOrgIds(Integer orgId) throws Exception {
        String hql = "select GROUP_CONCAT(OrgId) from tb_org_father_and_child where MainOrgId =" + orgId;
        Query query = em.createNativeQuery(hql);
        String orgIds = "";
        Object object = query.getSingleResult();
        if (object != null) {
            orgIds = object.toString();
        }
        return orgIds;
    }

    public String getOrgIds() throws Exception {
        String hql = "select GROUP_CONCAT(OrgId) from tb_org";
        Query query = em.createNativeQuery(hql);
        String orgIds = "";
        Object object = query.getSingleResult();
        if (object != null) {
            orgIds = object.toString();
        }
        return orgIds;
    }

    public List<OrgDto> getAllOrg(String orgIds) throws Exception {
        String hql = "select o.orgId,o.orgName from tb_org o where o.OrgType =1 and o.orgId in (" + orgIds + ")";
        hql += " order by convert(o.orgName using gbk) asc";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(OrgDto.class));
        List<OrgDto> org = query.getResultList();
        return org;
    }

    public List<String> getAllAreaName(String orgIds, String provinceName, String cityName) throws Exception {
        String hql = "select distinct r.area from tb_roadpole r where (r.OrgId IN (" + orgIds + ") or r.MainOrgId IN (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and r.Province ='" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and r.City ='" + cityName + "'";
        }
        hql += " order by convert(r.area using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> area = query.getResultList();
        return area;
    }

    public List<String> getRoadNames(String provinceName, String cityName, String areaName, String orgId, Integer actor) throws Exception {
        String hql = "select distinct r.roadName from tb_roadpole r where (r.orgId in (" + orgId + ") or " +
                "r.mainOrgId in (" + orgId + "))";
        if (!provinceName.equals("-1")) {
            hql += " and r.Province = '" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and r.City = '" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and r.area = '" + areaName + "'";
        }
        hql += " order by convert(r.roadName using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<OrgDto> getDealOrg(String orgIds) throws Exception {
        String hql = "select o.orgId,o.orgName from tb_org o where o.OrgType =0 and o.orgId in (" + orgIds + ")";
        hql += " order by convert(o.orgName using gbk) asc";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(OrgDto.class));
        List<OrgDto> org = query.getResultList();
        return org;
    }

    public List<String> getOrgYearDealTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.maintenanceTime from tb_maintenance m,tb_user u where m.maintenanceStatus = 2 and " +
                "(m.maintenanceTime between '" + year + "' and '" + yearNext + "') and m.maintenancePeopleId = u.userId and u.orgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<MaintenanceDto> getOrgyearSpendMoney(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.maintenanceTime,m.repairAmount from tb_maintenance m,tb_user u where " +
                "(m.maintenanceTime between '" + year + "' and '" + yearNext + "') and m.maintenancePeopleId = u.userId and u.orgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<String> getOrgyearTalkTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select s.signFaultTime from tb_signfault s where " +
                "(s.signFaultTime between '" + year + "' and '" + yearNext + "') and  (s.orgId in (" + orgIds + ") or " +
                "s.mainOrgId in (" + orgIds + "))";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<OrgDto> getConservationOrg(Integer mainOrgId, String orgIds, Integer actor) throws Exception {
        String hql = "select o.orgId,o.orgName from tb_org o where o.OrgType =0 and o.orgId in (" + orgIds + ")" +
                " order by convert(o.orgName using gbk) asc";
        if (mainOrgId != -1) {
            hql = "SELECT o.OrgId,o.OrgName FROM tb_org_father_and_child c,tb_org o WHERE " +
                    "c.OrgId = o.OrgId AND c.MainOrgId = " + mainOrgId + " order by convert(o.orgName using gbk) asc";
        }
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(OrgDto.class));
        List<OrgDto> org = query.getResultList();
        return org;
    }

    public List<String> getProvinceName(String orgIds) throws Exception {
        String hql = "select distinct r.Province from tb_roadpole r where r.OrgId in (" + orgIds + ") or " +
                "r.MainOrgId in (" + orgIds + ")";
        hql += " order by convert(r.Province using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> province = query.getResultList();
        return province;
    }

    public List<String> getCityName(String orgIds, String provinceName) throws Exception {
        String hql = "select distinct r.City from tb_roadpole r where (r.OrgId in (" + orgIds + ") or " +
                "r.MainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and r.Province = '" + provinceName + "'";
        }
        hql += " order by convert(r.City using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> city = query.getResultList();
        return city;
    }

    public List<String> getStartRoadName(String orgIds, String provinceName, String cityName, String areaName,
                                         String endRoadName, String roadName) throws Exception {
        String hql = "select distinct r.StartRoad from tb_roadpole r where (r.OrgId in (" + orgIds + ") or " +
                "r.MainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and r.Province = '" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and r.City = '" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and r.area = '" + areaName + "'";
        }
        if (!endRoadName.equals("-1")) {
            hql += " and r.EndRoad = '" + endRoadName + "'";
        }
        if (!roadName.equals("-1")) {
            hql += " and r.RoadName = '" + roadName + "'";
        }
        hql += " order by convert(r.StartRoad using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<String> getEndRoadName(String orgIds, String startRoad, String provinceName, String cityName,
                                       String areaName, String roadName) throws Exception {
        String hql = "select distinct r.EndRoad from tb_roadpole r where (r.OrgId in (" + orgIds + ") or " +
                "r.MainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and r.Province = '" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and r.City = '" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and r.area = '" + areaName + "'";
        }
        if (!startRoad.equals("-1")) {
            hql += " and r.StartRoad = '" + startRoad + "'";
        }
        if (!roadName.equals("-1")) {
            hql += " and r.RoadName = '" + roadName + "'";
        }
        hql += " order by convert(r.EndRoad using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<Integer> getPerSignCounts(String orgIds, String time, String province, String city, String area, String road) throws Exception {
        String[] timeLimit = {"", ""};
        timeLimit = time.split(" - ");
        String hql = "select r.RoadSignType from tb_roadsign r ,tb_roadpole p where (r.OrgId in (" + orgIds + ") or " +
                "r.MainOrgId in (" + orgIds + ")) AND (r.RoadSignInstallTime between '" + timeLimit[0] + "' and '" + timeLimit[1] + "') " +
                " and p.RoadPoleId=r.RoadPoleId and p.Province like '%" + province + "%' and p.city like '%" + city + "%' " +
                " and p.Area like '%" + area + "%' and p.RoadName like '%" + road + "%'   ORDER BY r.RoadSignType";
        Query query = em.createNativeQuery(hql);
        List<Integer> counts = query.getResultList();
        return counts;
    }

    public Integer getRoadPoleCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_roadpole  ";
        if (actorId != 1)
            sql += " where orgId=" + orgId + " or mainOrgId=" + orgId + " ";
        Query query = em.createNativeQuery(sql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public Integer getRoadSignCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_roadsign ";
        if (actorId != 1)
            sql += " where orgId=" + orgId + " or mainOrgId=" + orgId + " ";
        Query query = em.createNativeQuery(sql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public Integer getCoverCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_cover ";
        if (actorId != 1)
            sql += " where coverOrgId=" + orgId + " or coverMainOrgId=" + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getHighLeverCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_highLever ";
        if (actorId != 1)
            sql += " where highLeverOrgId=" + orgId + " or highLeverMainOrgId=" + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getGuardrailCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_guardrail ";
        if (actorId != 1)
            sql += " where guardrailOrgId=" + orgId + " or guardrailMainOrgId=" + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getMonitorCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_monitor ";
        if (actorId != 1)
            sql += " where monitorOrgId = " + orgId + " or monitorMainOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getRoadLineCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_road_line ";
        if (actorId != 1)
            sql += " where roadLineOrgId = " + orgId + " or roadLineMainOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getSignalLampCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_signal_lamp ";
        if (actorId != 1)
            sql += " where signalLampOrgId = " + orgId + " or signalLampMainOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getSignalMachineCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_signal_machine ";
        if (actorId != 1)
            sql += " where signalMachineOrgId = " + orgId + " or signalMachineMainOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getSolarEnergyCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_solar_energy ";
        if (actorId != 1)
            sql += " where solarEnergyOrgId = " + orgId + " or solarEnergyMainOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getRoadSignPole4DTCount(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "SELECT COUNT(DISTINCT p.Province,p.City,p.Area,p.RoadName) FROM tb_roadpole p,tb_roadsign s WHERE (s.OrgId in (" + orgIds + ") or " +
                "s.MainOrgId in (" + orgIds + ")) AND s.RoadPoleId = p.RoadPoleId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:道路 4:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and p.Province = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and p.City = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and p.Area = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and p.RoadName ='" + sQuery[3] + "'";
        }
        if (!sQuery[4].equals("-1")) {
            hql += " and p.OrgId ='" + sQuery[4] + "'";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<RoadpoleDto> getRoadSignPole4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "SELECT p.city,p.province,p.area,p.RoadName,o.OrgName FROM " +
                "tb_roadsign AS s INNER JOIN tb_roadpole AS p ON p.RoadPoleId = s.RoadPoleId" +
                " INNER JOIN tb_org o ON p.OrgId = o.OrgId where (s.OrgId in (" + orgIds + ") or " +
                "s.MainOrgId in (" + orgIds + "))";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:道路 4:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and p.Province = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and p.City = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and p.Area = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and p.RoadName ='" + sQuery[3] + "'";
        }
        if (!sQuery[4].equals("-1")) {
            hql += " and p.OrgId ='" + sQuery[4] + "'";
        }
        hql += " GROUP BY p.Province,p.City,p.Area,p.RoadName ORDER BY p.Province,p.City,p.Area,p.RoadName asc";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        List<RoadpoleDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public List<BigInteger> getRoadSignCounts(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "SELECT count(s.RoadSignId) FROM " +
                "tb_roadsign AS s INNER JOIN tb_roadpole AS p ON p.RoadPoleId = s.RoadPoleId" +
                " where  (s.OrgId in (" + orgIds + ") or " +
                "s.MainOrgId in (" + orgIds + "))";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:道路 4:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and p.Province = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and p.City = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and p.Area = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and p.RoadName ='" + sQuery[3] + "'";
        }
        if (!sQuery[4].equals("-1")) {
            hql += " and p.OrgId ='" + sQuery[4] + "'";
        }
        hql += " GROUP BY p.Province,p.City,p.Area,p.RoadName ORDER BY p.Province,p.City,p.Area,p.RoadName asc";
        Query query = em.createNativeQuery(hql);
        List<BigInteger> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public List<NameCountDto> getAreaRoadSignCount(String orgIds, String roadSignCity) throws Exception {
        String hql = "SELECT COUNT(s.RoadSignId),p.Area FROM tb_roadsign s RIGHT JOIN tb_roadpole p " +
                "ON s.RoadPoleId = p.RoadPoleId WHERE p.City = '" + roadSignCity + "'" +
                " AND  (s.OrgId in (" + orgIds + ") or " +
                "s.MainOrgId in (" + orgIds + ")) GROUP BY p.Area ORDER BY convert(p.Area using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<NameCountDto> nameCountDtos = query.getResultList();
        return nameCountDtos;
    }

    public List<RoadpoleDto> getRoadPole4Excel(String strSearch, Integer actor, String orgIds) throws Exception {
        String hql = "select r.RoadPoleId,r.RoadPolePic,r.Province,r.City,r.Area,r.RoadName,m.OrgName as mainOrgName,o.OrgName as orgName,r.Longitude," +
                "r.Latitude,r.RoadPoleBridge,r.RoadPoleIsComplete,r.RoadPoleSecurity,r.RoadPoleCreateTime,r.StartRoad,r.EndRoad from tb_roadpole r " +
                "LEFT JOIN tb_org m ON r.MainOrgId = m.OrgId " +
                "LEFT JOIN tb_org o ON r.OrgId = o.OrgId " +
                "where r.RoadPoleId LIKE '%'";
        String[] sQuery = convertParams(strSearch);
        //0:省 1:市 2:区域 3:道路 4:起始路段 5:终止路段 6:主管单位 7:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and r.Province = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and r.City = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and r.Area = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and r.RoadName ='" + sQuery[3] + "'";
        }
        if (!sQuery[4].equals("-1") && sQuery[5].equals("-1")) {
            hql += " and (r.StartRoad ='" + sQuery[4] + "' or r.EndRoad ='" + sQuery[4] + "')";
        }
        if (sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and (r.EndRoad ='" + sQuery[5] + "' or r.StartRoad ='" + sQuery[5] + "')";
        }
        if (!sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and ((r.StartRoad ='" + sQuery[4] + "' and r.EndRoad ='" + sQuery[5] + "') or" +
                    "(r.StartRoad = '" + sQuery[5] + "' and r.EndRoad = '" + sQuery[4] + "'))";
        }
        if (!sQuery[6].equals("-1")) {
            hql += " and r.MainOrgId = '" + sQuery[6] + "'";
        }
        if (!sQuery[7].equals("-1")) {
            hql += " and r.OrgId = '" + sQuery[7] + "'";
        }
        if (actor != 1) {
            hql += " and (r.OrgId IN (" + orgIds + ") or " +
                    "r.MainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY r.RoadPoleCreateTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        List<RoadpoleDto> roadpoleDto = query.getResultList();
        return roadpoleDto;
    }

    public Integer getSignFaultCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_signfault ";
        if (actorId == 1)
            sql += "where companyCheck = 0 or mainCheck = 0";
        else if (actorId == 7)
            sql += "where companyCheck = 1 and mainCheck = 0 and mainOrgId = " + orgId;
        else
            sql += "where companyCheck = 0 and orgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }


    public Integer getCoverAuditCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_cover where coverIsComplete = 0 ";
        if (actorId != 1)
            sql += "and coverOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getCoverFaultCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_cover_fault ";
        if (actorId == 1)
            sql += "where coverCheck = 0 or coverMainCheck = 0";
        else if (actorId == 7)
            sql += "where coverCheck = 1 and coverMainCheck = 0 and coverMainOrgId = " + orgId;
        else
            sql += "where coverCheck = 0 and coverOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getHighLeverAuditCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_highLever where highLeverIsComplete = 0 ";
        if (actorId != 1)
            sql += "and highLeverOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getHighLeverFaultCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_highLever_fault ";
        if (actorId == 1)
            sql += "where highLeverCheck = 0 or highLeverMainCheck = 0";
        else if (actorId == 7)
            sql += "where highLeverCheck = 1 and highLeverMainCheck = 0 and highLeverMainOrgId = " + orgId;
        else
            sql += "where highLeverCheck = 0 and highLeverOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getGuardrailAuditCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_guardrail where guardrailIsComplete = 0 ";
        if (actorId != 1)
            sql += "and guardrailOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getGuardrailFaultCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_guardrail_fault ";
        if (actorId == 1)
            sql += "where guardrailCheck = 0 or guardrailMainCheck = 0";
        else if (actorId == 7)
            sql += "where guardrailCheck = 1 and guardrailMainCheck = 0 and guardrailMainOrgId = " + orgId;
        else
            sql += "where guardrailCheck = 0 and guardrailOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getMonitorAuditCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_monitor where monitorIsComplete = 0 ";
        if (actorId != 1)
            sql += "and monitorOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getMonitorFaultCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_monitor_fault ";
        if (actorId == 1)
            sql += "where monitorCheck = 0 or monitorMainCheck = 0";
        else if (actorId == 7)
            sql += "where monitorCheck = 1 and monitorMainCheck = 0 and monitorMainOrgId = " + orgId;
        else
            sql += "where monitorCheck = 0 and monitorOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getRoadLineAuditCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_road_line where roadLineIsComplete = 0 ";
        if (actorId != 1)
            sql += "and roadLineOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getRoadLineFaultCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_line_fault ";
        if (actorId == 1)
            sql += "where roadLineCheck = 0 or roadLineMainCheck = 0";
        else if (actorId == 7)
            sql += "where roadLineCheck = 1 and roadLineMainCheck = 0 and roadLineMainOrgId = " + orgId;
        else
            sql += "where roadLineCheck = 0 and roadLineOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getSignalLampAuditCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_signal_lamp where signalLampIsComplete = 0 ";
        if (actorId != 1)
            sql += "and signalLampOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getSignalLampFaultCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_lamp_fault ";
        if (actorId == 1)
            sql += "where lampCheck = 0 or lampMainCheck = 0";
        else if (actorId == 7)
            sql += "where lampCheck = 1 and lampMainCheck = 0 and lampMainOrgId = " + orgId;
        else
            sql += "where lampCheck = 0 and lampOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getSignalMachineAuditCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_signal_machine where signalMachineIsComplete = 0 ";
        if (actorId != 1)
            sql += "and signalMachineOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getSignalMachineFaultCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_machine_fault ";
        if (actorId == 1)
            sql += "where machineCheck = 0 or machineMainCheck = 0";
        else if (actorId == 7)
            sql += "where machineCheck = 1 and machineMainCheck = 0 and machineMainOrgId = " + orgId;
        else
            sql += "where machineCheck = 0 and machineOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getSolarEnergyAuditCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_solar_energy where solarEnergyIsComplete = 0 ";
        if (actorId != 1)
            sql += "and solarEnergyOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public Integer getSolarEnergyFaultCount(Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) from tb_solar_fault ";
        if (actorId == 1)
            sql += "where solarCheck = 0 or solarMainCheck = 0";
        else if (actorId == 7)
            sql += "where solarCheck = 1 and solarMainCheck = 0 and solarMainOrgId = " + orgId;
        else
            sql += "where solarCheck = 0 and solarOrgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<String> getCoverOrgYearDealTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.coverMaintenanceTime from tb_cover_fault m where m.coverMaintenanceStatus = 2 and " +
                "(m.CoverMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.coverOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getHighLeverOrgYearDealTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.highLeverMaintenanceTime from tb_highLever_fault m where m.highLeverMaintenanceStatus = 2 and " +
                "(m.HighLeverMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.highLeverOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getGuardrailOrgYearDealTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.guardrailMaintenanceTime from tb_guardrail_fault m where m.guardrailMaintenanceStatus = 2 and " +
                "(m.guardrailMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.guardrailOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getMonitorOrgYearDealTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.monitorMaintenanceTime from tb_monitor_fault m where m.monitorMaintenanceStatus = 2 and " +
                "(m.monitorMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.monitorOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getRoadLineOrgYearDealTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.roadLineMaintenanceTime from tb_line_fault m where m.roadLineMaintenanceStatus = 2 and " +
                "(m.roadLineMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.roadLineOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getSignLampOrgYearDealTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.lampMaintenanceTime from tb_lamp_fault m where m.lampMaintenanceStatus = 2 and " +
                "(m.lampMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.lampOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getSignalMachineOrgYearDealTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.machineMaintenanceTime from tb_machine_fault m where m.machineMaintenanceStatus = 2 and " +
                "(m.machineMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.machineOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getSolarEnergyOrgYearDealTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.solarMaintenanceTime from tb_solar_fault m where m.solarMaintenanceStatus = 2 and " +
                "(m.solarMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.solarOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getCoverOrgyearTalkTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.coverMaintenanceTime from tb_cover_fault m where " +
                "(m.CoverMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.coverOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getHighLeverOrgyearTalkTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.highLeverMaintenanceTime from tb_highLever_fault m where " +
                "(m.HighLeverMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.highLeverOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getGuardrailOrgyearTalkTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.guardrailMaintenanceTime from tb_guardrail_fault m where " +
                "(m.guardrailMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.guardrailOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getMonitorOrgyearTalkTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.monitorMaintenanceTime from tb_monitor_fault m where " +
                "(m.monitorMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.monitorOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getRoadLineOrgyearTalkTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.roadLineMaintenanceTime from tb_line_fault m where " +
                "(m.roadLineMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.roadLineOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getSignLampOrgyearTalkTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.lampMaintenanceTime from tb_lamp_fault m where " +
                "(m.lampMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.lampOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getSignalMachineOrgyearTalkTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.machineMaintenanceTime from tb_machine_fault m where " +
                "(m.machineMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.machineOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<String> getSolarEnergyOrgyearTalkTimes(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.solarMaintenanceTime from tb_solar_fault m where " +
                "(m.solarMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.solarOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<MaintenanceDto> getCoverOrgyearSpendMoney(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.coverMaintenanceTime as maintenanceTime, m.coverMoney as repairAmount from tb_cover_fault m where " +
                "(m.coverMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.coverOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<MaintenanceDto> getHighLeverOrgyearSpendMoney(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.highLeverMaintenanceTime as maintenanceTime, m.highLeverMoney as repairAmount from tb_highLever_fault m where " +
                "(m.highLeverMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.highLeverOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<MaintenanceDto> getGuardrailOrgyearSpendMoney(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.guardrailMaintenanceTime as maintenanceTime, m.guardrailMoney as repairAmount from tb_guardrail_fault m where " +
                "(m.guardrailMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.guardrailOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<MaintenanceDto> getMonitorOrgyearSpendMoney(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.monitorMaintenanceTime as maintenanceTime, m.monitorMoney as repairAmount from tb_monitor_fault m where " +
                "(m.monitorMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.monitorOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<MaintenanceDto> getRoadLineOrgyearSpendMoney(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.roadLineMaintenanceTime as maintenanceTime, m.roadLineMoney as repairAmount from tb_line_fault m where " +
                "(m.roadLineMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.roadLineOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<MaintenanceDto> getSignLampOrgyearSpendMoney(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.lampMaintenanceTime as maintenanceTime, m.lampMoney as repairAmount from tb_lamp_fault m where " +
                "(m.lampMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.lampOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<MaintenanceDto> getSignalMachineOrgyearSpendMoney(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.machineMaintenanceTime as maintenanceTime, m.machineMoney as repairAmount from tb_machine_fault m where " +
                "(m.machineMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.machineOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<MaintenanceDto> getSolarEnergyOrgyearSpendMoney(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.solarMaintenanceTime as maintenanceTime, m.solarMoney as repairAmount from tb_solar_fault m where " +
                "(m.solarMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.solarOrgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(MaintenanceDto.class));
        List<MaintenanceDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<String> getAllInfo4Type(String type, Integer equipmentType, String orgIds) throws Exception {
        String sql = "";
        switch (equipmentType) {
            case 0:
            case 1:
                sql = "select DISTINCT rp." + type + " as " + type + "\n" +
                        "from tb_roadpole rp where orgId in (" + orgIds + ")\n";
                break;
            case 2:
                if (type.contains("RoadName"))
                    sql = "";
                else
                    sql = "select DISTINCT SignalLamp" + type + " as " + type + "\n" +
                            "from tb_signal_lamp where SignalLampOrgId in (" + orgIds + ")\n";
                break;
            case 3:
                if (type.contains("RoadName"))
                    sql = "";
                else
                    sql = "select DISTINCT SignalMachine" + type + " as " + type + "\n" +
                            "from tb_signal_machine where SignalMachineOrgId in (" + orgIds + ")\n";
                break;
            case 4:
                sql = "select DISTINCT SolarEnergy" + type + " as " + type + "\n" +
                        "from tb_solar_energy where SolarEnergyOrgId in (" + orgIds + ")\n";
                break;
            case 5:
                sql = "select DISTINCT highLever" + type + " as " + type + "\n" +
                        "from tb_highLever where highLeverOrgId in (" + orgIds + ")";
                break;
            case 6:
                sql = "select DISTINCT RoadLine" + type + " as " + type + "\n" +
                        "from tb_road_line where RoadLineOrgId in (" + orgIds + ")\n";
                break;
            case 7:
                sql = "select DISTINCT guardrail" + type + " as " + type + "\n" +
                        "from tb_guardrail where guardrailOrgId in (" + orgIds + ")\n";
                break;
            case 8:
                sql = "select DISTINCT monitor" + type + " as " + type + "\n" +
                        "from tb_monitor where monitorOrgId in (" + orgIds + ")\n";
                break;
            case 9:
                sql = "select DISTINCT cover" + type + " as " + type + "\n" +
                        "from tb_cover where coverOrgId in (" + orgIds + ")";
                break;
            default:
                sql = "select rp." + type + " as " + type + "\n" +
                        "from tb_roadpole rp where orgId in (" + orgIds + ")\n" +
                        "union \n" +
                        "select SolarEnergy" + type + " as " + type + "\n" +
                        "from tb_solar_energy where SolarEnergyOrgId in (" + orgIds + ")\n" +
                        "union \n" +
                        "select RoadLine" + type + " as " + type + "\n" +
                        "from tb_road_line where RoadLineOrgId in (" + orgIds + ")\n" +
                        "union \n" +
                        "select guardrail" + type + " as " + type + "\n" +
                        "from tb_guardrail where guardrailOrgId in (" + orgIds + ")\n" +
                        "union \n" +
                        "select monitor" + type + " as " + type + "\n" +
                        "from tb_monitor where monitorOrgId in (" + orgIds + ")\n" +
                        "union \n" +
                        "select cover" + type + " as " + type + "\n" +
                        "from tb_cover where coverOrgId in (" + orgIds + ")" +
                        "union \n" +
                        "select highLever" + type + " as " + type + "\n" +
                        "from tb_highLever where highLeverOrgId in (" + orgIds + ")";
                if (!type.contains("RoadName"))
                    sql += "union \n" +
                            "select SignalLamp" + type + " as " + type + "\n" +
                            "from tb_signal_lamp where SignalLampOrgId in (" + orgIds + ")\n" +
                            "union \n" +
                            "select SignalMachine" + type + " as " + type + "\n" +
                            "from tb_signal_machine where SignalMachineOrgId in (" + orgIds + ")\n";
                break;
        }
        if (sql.length() == 0) return null;
        Query query = em.createNativeQuery(sql);
        List<String> result = query.getResultList();
        return result;
    }

    public List<ColligateDto> getAllRoadSignCountDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "select count(r.RoadSignId) as count,a.Province,a.City,a.Area,a.roadName,a.StartRoad,a.EndRoad\n" +
                "from ( SELECT RoadPoleId,ProjectNumber,Province,City,Area,RoadName,StartRoad,EndRoad from tb_roadpole ) as a,tb_roadsign as r\n" +
                "where a.RoadPoleId=r.RoadPoleId and RoadSignType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and a.Province=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and a.City=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and a.Area=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and a.RoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and a.StartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and a.EndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and r.orgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and r.mainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and a.projectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and r.RoadSignInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP  BY  a.Province,a.City,a.Area,a.RoadName,a.StartRoad,a.EndRoad ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllRoadSignCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "select count(r.RoadSignId) as count,a.Province,a.City,a.Area,a.StartRoad,a.EndRoad\n" +
                "from ( SELECT RoadPoleId,ProjectNumber,Province,City,Area,RoadName,StartRoad,EndRoad from tb_roadpole ) as a,tb_roadsign as r\n" +
                "where a.RoadPoleId=r.RoadPoleId and r.RoadSignType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and a.Province=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and a.City=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and a.Area=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and a.RoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and a.StartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and a.EndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and r.orgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and r.mainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and a.projectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and r.RoadSignInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP  BY  a.Province,a.City,a.Area,a.RoadName,a.StartRoad,a.EndRoad ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllRoadPoleCountDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count,Province,City,Area,RoadName,StartRoad,EndRoad from tb_roadpole " +
                " where RoadPoleShape in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and Province=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and City=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and Area=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and RoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and StartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and EndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and orgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and mainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and projectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and RoadPoleCreateTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY Province,City,Area,RoadName,StartRoad,EndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllRoadPoleCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count,Province,City,Area,RoadName,StartRoad,EndRoad from tb_roadpole " +
                " where RoadPoleShape in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and Province=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and City=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and Area=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and RoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and StartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and EndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and orgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and mainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and projectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and RoadPoleCreateTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY Province,City,Area,RoadName,StartRoad,EndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }


    public List<ColligateDto> getAllSignalLampCountDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,SignalLampProvince as Province, SignalLampCity as City,SignalLampArea as Area,SignalLampStartRoad as StartRoad,SignalLampEndRoad as EndRoad\n" +
                "FROM tb_signal_lamp where SignalLampType like \'%" + dto.getSecondType() + "%\' ";
        if (!dto.getProvince().equals("-1"))
            sql += " and SignalLampProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SignalLampCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SignalLampArea=\'" + dto.getArea() + "\' ";
       /* if (!dto.getRoadName().equals("-1"))
            sql += " and RoadName=\'" + dto.getRoadName() + "\' ";*/
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SignalLampStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SignalLampEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SignalLampOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SignalLampMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and SignalLampInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY SignalLampProvince, SignalLampCity,SignalLampArea,SignalLampStartRoad,SignalLampEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllSignalLampCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,SignalLampProvince as Province, SignalLampCity as City,SignalLampArea as Area,SignalLampStartRoad as StartRoad,SignalLampEndRoad as EndRoad\n" +
                "FROM tb_signal_lamp where SignalLampType like \'%" + dto.getSecondType() + "%\' ";
        if (!dto.getProvince().equals("-1"))
            sql += " and SignalLampProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SignalLampCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SignalLampArea=\'" + dto.getArea() + "\' ";
       /* if (!dto.getRoadName().equals("-1"))
            sql += " and RoadName=\'" + dto.getRoadName() + "\' ";*/
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SignalLampStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SignalLampEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SignalLampOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SignalLampMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and SignalLampInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY SignalLampProvince, SignalLampCity,SignalLampArea,SignalLampStartRoad,SignalLampEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllSignalMachineCountDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,SignalMachineProvince as Province, SignalMachineCity as City,SignalMachineArea as Area,SignalMachineStartRoad as StartRoad,SignalMachineEndRoad as EndRoad\n" +
                "FROM tb_signal_machine where SignalMachineType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and SignalMachineProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SignalMachineCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SignalMachineArea=\'" + dto.getArea() + "\' ";
       /* if (!dto.getRoadName().equals("-1"))
            sql += " and RoadName=\'" + dto.getRoadName() + "\' ";*/
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SignalMachineStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SignalMachineEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SignalMachineOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SignalMachineMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and SignalMachineInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY SignalMachineProvince, SignalMachineCity,SignalMachineArea,SignalMachineStartRoad,SignalMachineEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllSignalMachineCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,SignalMachineProvince as Province, SignalMachineCity as City," +
                "SignalMachineArea as Area,SignalMachineStartRoad as StartRoad,SignalMachineEndRoad as EndRoad\n" +
                "FROM tb_signal_machine where SignalMachineType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and SignalMachineProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SignalMachineCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SignalMachineArea=\'" + dto.getArea() + "\' ";
       /* if (!dto.getRoadName().equals("-1"))
            sql += " and RoadName=\'" + dto.getRoadName() + "\' ";*/
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SignalMachineStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SignalMachineEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SignalMachineOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SignalMachineMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and SignalMachineInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY SignalMachineProvince, SignalMachineCity,SignalMachineArea,SignalMachineStartRoad,SignalMachineEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllSolarEnergyCountDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,SolarEnergyProvince as Province, SolarEnergyCity as City," +
                "SolarEnergyArea as Area,SolarEnergySolarEnergyRoadName as RoadName,SolarEnergyStartRoad as StartRoad,SolarEnergyEndRoad as EndRoad\n" +
                "FROM tb_solar_energy where SolarEnergyType in (" + dto.getSecondType() + ")  ";
        if (!dto.getProvince().equals("-1"))
            sql += " and SolarEnergyProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SolarEnergyCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SolarEnergyArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and SolarEnergyRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SolarEnergyStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SolarEnergyEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SolarEnergyOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SolarEnergyMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and SolarEnergyInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY SolarEnergyProvince, SolarEnergyCity,SolarEnergyArea,SolarEnergyStartRoad,SolarEnergyEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllSolarEnergyCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,SolarEnergyProvince as Province, SolarEnergyCity as City," +
                "SolarEnergyArea as Area,SolarEnergyRoadName as RoadName,SolarEnergyStartRoad as StartRoad,SolarEnergyEndRoad as EndRoad\n" +
                "FROM tb_solar_energy where SolarEnergyType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and SolarEnergyProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SolarEnergyCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SolarEnergyArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and SolarEnergyRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SolarEnergyStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SolarEnergyEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SolarEnergyOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SolarEnergyMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and SolarEnergyInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY SolarEnergyProvince, SolarEnergyCity,SolarEnergyArea,SolarEnergyStartRoad,SolarEnergyEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllRoadLineCountDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,RoadLineProvince as Province, RoadLineCity as City," +
                "RoadLineArea as Area,RoadLineRoadName as RoadName,RoadLineStartRoad as StartRoad,RoadLineEndRoad as EndRoad\n" +
                "FROM tb_road_line where RoadLineType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and RoadLineProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and RoadLineCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and RoadLineArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and RoadLineRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and RoadLineStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and RoadLineEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and RoadLineOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and RoadLineMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and RoadLineInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY RoadLineProvince, RoadLineCity,RoadLineArea,RoadLineStartRoad,RoadLineEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllRoadLineCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,RoadLineProvince as Province, RoadLineCity as City," +
                "RoadLineArea as Area,RoadLineRoadName as RoadName,RoadLineStartRoad as StartRoad,RoadLineEndRoad as EndRoad\n" +
                "FROM tb_road_line where RoadLineType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and RoadLineProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and RoadLineCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and RoadLineArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and RoadLineRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and RoadLineStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and RoadLineEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and RoadLineOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and RoadLineMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and RoadLineInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY RoadLineProvince, RoadLineCity,RoadLineArea,RoadLineStartRoad,RoadLineEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllGuardrailCountDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,GuardrailProvince as Province, GuardrailCity as City," +
                "GuardrailArea as Area,GuardrailRoadName as RoadName,GuardrailStartRoad as StartRoad,GuardrailEndRoad as EndRoad\n" +
                "FROM tb_guardrail where GuardrailType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and GuardrailProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and GuardrailCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and GuardrailArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and GuardrailRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and GuardrailStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and GuardrailEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and GuardrailOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and GuardrailMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and GuardrailInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY GuardrailProvince, GuardrailCity,GuardrailArea,GuardrailStartRoad,GuardrailEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllGuardrailCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,GuardrailProvince as Province, GuardrailCity as City," +
                "GuardrailArea as Area,GuardrailRoadName as RoadName,GuardrailStartRoad as StartRoad,GuardrailEndRoad as EndRoad\n" +
                "FROM tb_guardrail where GuardrailType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and GuardrailProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and GuardrailCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and GuardrailArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and GuardrailRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and GuardrailStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and GuardrailEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and GuardrailOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and GuardrailMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and GuardrailInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY GuardrailProvince, GuardrailCity,GuardrailArea,GuardrailStartRoad,GuardrailEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllMonitorCountDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,MonitorProvince as Province, MonitorCity as City," +
                "MonitorArea as Area,MonitorRoadName as RoadName,MonitorStartRoad as StartRoad,MonitorEndRoad as EndRoad\n" +
                "FROM tb_monitor where MonitorPoleShape in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and MonitorProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and MonitorCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and MonitorArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and MonitorRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and MonitorStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and MonitorEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and MonitorOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and MonitorMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and MonitorInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY MonitorProvince, MonitorCity,MonitorArea,MonitorStartRoad,MonitorEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllMonitorCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,MonitorProvince as Province, MonitorCity as City," +
                "MonitorArea as Area,MonitorRoadName as RoadName,MonitorStartRoad as StartRoad,MonitorEndRoad as EndRoad\n" +
                "FROM tb_monitor where MonitorPoleShape in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and MonitorProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and MonitorCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and MonitorArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and MonitorRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and MonitorStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and MonitorEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and MonitorOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and MonitorMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and MonitorInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY MonitorProvince, MonitorCity,MonitorArea,MonitorStartRoad,MonitorEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllCoverCountDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,CoverProvince as Province, CoverCity as City," +
                "CoverArea as Area,CoverRoadName as RoadName,CoverStartRoad as StartRoad,CoverEndRoad as EndRoad\n" +
                "FROM tb_cover where CoverShape in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and CoverProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and CoverCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and CoverArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and CoverRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and CoverStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and CoverEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and CoverOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and CoverMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and CoverInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY CoverProvince, CoverCity,CoverArea,CoverStartRoad,CoverEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public List<ColligateDto> getAllHighLeverCountDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,HighLeverProvince as Province, HighLeverCity as City," +
                "HighLeverArea as Area,HighLeverRoadName as RoadName,HighLeverStartRoad as StartRoad,HighLeverEndRoad as EndRoad\n" +
                "FROM tb_highLever where HighLeverShape in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and HighLeverProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and HighLeverCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and HighLeverArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and HighLeverRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and HighLeverStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and HighLeverEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and HighLeverOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and HighLeverMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and HighLeverInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY HighLeverProvince, HighLeverCity,HighLeverArea,HighLeverStartRoad,HighLeverEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllCoverCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,CoverProvince as Province, CoverCity as City," +
                "CoverArea as Area,CoverRoadName as RoadName,CoverStartRoad as StartRoad,CoverEndRoad as EndRoad\n" +
                "FROM tb_cover where CoverShape in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and CoverProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and CoverCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and CoverArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and CoverRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and CoverStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and CoverEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and CoverOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and CoverMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and CoverInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY CoverProvince, CoverCity,CoverArea,CoverStartRoad,CoverEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public Integer getAllHighLeverCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*) as count ,HighLeverProvince as Province, HighLeverCity as City," +
                "HighLeverArea as Area,HighLeverRoadName as RoadName,HighLeverStartRoad as StartRoad,HighLeverEndRoad as EndRoad\n" +
                "FROM tb_highLever where HighLeverShape in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and HighLeverProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and HighLeverCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and HighLeverArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and HighLeverRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and HighLeverStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and HighLeverEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and HighLeverOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and HighLeverMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getProjectNumber().length() != 0)
            sql += " and ProjectNumber=" + dto.getProjectNumber() + " ";
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and HighLeverInstallTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += "GROUP BY HighLeverProvince, HighLeverCity,HighLeverArea,HighLeverStartRoad,HighLeverEndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }


    public List<ColligateDto> getAllSolarEnergyFaultDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,SolarEnergyProvince as Province,SolarEnergyCity as City,SolarEnergyArea as Area," +
                "SolarEnergyRoadName as RoadName,SolarEnergyStartRoad as StartRoad,SolarEnergyEndRoad as EndRoad,SUM(SolarMoney) as Money\n" +
                " FROM tb_solar_energy as a,tb_solar_fault e WHERE a.SolarEnergyId=e.SolarEnergyId and a.SolarEnergyType in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and SolarEnergyProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SolarEnergyCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SolarEnergyArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and SolarEnergyRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SolarEnergyStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SolarEnergyEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SolarEnergyOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SolarEnergyMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.SolarTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY SolarEnergyProvince,SolarEnergyCity,SolarEnergyArea,SolarEnergyRoadName,SolarEnergyStartRoad,SolarEnergyEndRoad ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllSolarEnergyFaultCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,SolarEnergyProvince as Province,SolarEnergyCity as City,SolarEnergyArea as Area," +
                "SolarEnergyRoadName as RoadName,SolarEnergyStartRoad as StartRoad,SolarEnergyEndRoad as EndRoad,SUM(SolarMoney) as Money\n" +
                " FROM tb_solar_energy as a,tb_solar_fault e WHERE a.SolarEnergyId=e.SolarEnergyId and a.SolarEnergyType in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and SolarEnergyProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SolarEnergyCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SolarEnergyArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and SolarEnergyRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SolarEnergyStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SolarEnergyEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SolarEnergyOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SolarEnergyMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.SolarTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY SolarEnergyProvince,SolarEnergyCity,SolarEnergyArea,SolarEnergyRoadName,SolarEnergyStartRoad,SolarEnergyEndRoad ";
        Query query = em.createNativeQuery(sql);
        // query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllMonitorFaultDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,MonitorProvince as Province,MonitorCity as City,MonitorArea as Area," +
                "MonitorRoadName as RoadName,MonitorStartRoad as StartRoad,MonitorEndRoad as EndRoad,SUM(MonitorMoney) as Money\n" +
                " FROM tb_monitor as a,tb_monitor_fault e WHERE a.MonitorId=e.MonitorId and a.MonitorPoleShape in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and MonitorProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and MonitorCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and MonitorArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and MonitorRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and MonitorStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and MonitorEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and MonitorOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and MonitorMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.MonitorTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY MonitorProvince,MonitorCity,MonitorArea,MonitorRoadName,MonitorStartRoad,MonitorEndRoad ";

        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllMonitorFaultCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,MonitorProvince as Province,MonitorCity as City,MonitorArea as Area," +
                "MonitorRoadName as RoadName,MonitorStartRoad as StartRoad,MonitorEndRoad as EndRoad,SUM(MonitorMoney) as Money\n" +
                " FROM tb_monitor as a,tb_monitor_fault e WHERE a.MonitorId=e.MonitorId and a.MonitorPoleShape in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and MonitorProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and MonitorCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and MonitorArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and MonitorRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and MonitorStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and MonitorEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and MonitorOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and MonitorMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.MonitorTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY MonitorProvince,MonitorCity,MonitorArea,MonitorRoadName,MonitorStartRoad,MonitorEndRoad ";

        Query query = em.createNativeQuery(sql);
        //query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllCoverFaultDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,CoverProvince as Province,CoverCity as City,CoverArea as Area," +
                "CoverRoadName as RoadName,CoverStartRoad as StartRoad,CoverEndRoad as EndRoad,SUM(CoverMoney) as Money\n" +
                "FROM tb_cover as a,tb_cover_fault e WHERE a.CoverId=e.CoverId and a.CoverShape in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and CoverProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and CoverCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and CoverArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and CoverRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and CoverStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and CoverEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and CoverOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and CoverMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.CoverFaultTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY CoverProvince,CoverCity,CoverArea,CoverRoadName,CoverStartRoad,CoverEndRoad ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllCoverFaultCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,CoverProvince as Province,CoverCity as City,CoverArea as Area," +
                "CoverRoadName as RoadName,CoverStartRoad as StartRoad,CoverEndRoad as EndRoad,SUM(CoverMoney) as Money\n" +
                " FROM tb_cover as a,tb_cover_fault e WHERE a.CoverId=e.CoverId and a.CoverShape in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and CoverProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and CoverCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and CoverArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and CoverRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and CoverStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and CoverEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and CoverOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and CoverMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.CoverFaultTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY CoverProvince,CoverCity,CoverArea,CoverRoadName,CoverStartRoad,CoverEndRoad ";
        Query query = em.createNativeQuery(sql);
        //  query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllHighLeverFaultDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,HighLeverProvince as Province,HighLeverCity as City,HighLeverArea as Area," +
                "HighLeverRoadName as RoadName,HighLeverStartRoad as StartRoad,HighLeverEndRoad as EndRoad,SUM(HighLeverMoney) as Money\n" +
                "FROM tb_highLever as a,tb_highLever_fault e WHERE a.HighLeverId=e.HighLeverId and a.HighLeverShape in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and HighLeverProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and HighLeverCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and HighLeverArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and HighLeverRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and HighLeverStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and HighLeverEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and HighLeverOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and HighLeverMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.HighLeverFaultTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY HighLeverProvince,HighLeverCity,HighLeverArea,HighLeverRoadName,HighLeverStartRoad,HighLeverEndRoad ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllHighLeverFaultCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,HighLeverProvince as Province,HighLeverCity as City,HighLeverArea as Area," +
                "HighLeverRoadName as RoadName,HighLeverStartRoad as StartRoad,HighLeverEndRoad as EndRoad,SUM(HighLeverMoney) as Money\n" +
                " FROM tb_highLever as a,tb_highLever_fault e WHERE a.HighLeverId=e.HighLeverId and a.HighLeverShape in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and HighLeverProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and HighLeverCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and HighLeverArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and HighLeverRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and HighLeverStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and HighLeverEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and HighLeverOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and HighLeverMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.HighLeverFaultTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY HighLeverProvince,HighLeverCity,HighLeverArea,HighLeverRoadName,HighLeverStartRoad,HighLeverEndRoad ";
        Query query = em.createNativeQuery(sql);
        //  query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllGuardrailFaultDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,GuardrailProvince as Province,GuardrailCity as City,GuardrailArea as Area," +
                "GuardrailRoadName as RoadName,GuardrailStartRoad as StartRoad,GuardrailEndRoad as EndRoad,SUM(GuardrailMoney) as Money\n" +
                " FROM tb_guardrail as a,tb_guardrail_fault e WHERE a.GuardrailId=e.GuardrailId and a.GuardrailType in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and GuardrailProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and GuardrailCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and GuardrailArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and GuardrailRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and GuardrailStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and GuardrailEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and GuardrailOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and GuardrailMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.GuardrailTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY GuardrailProvince,GuardrailCity,GuardrailArea,GuardrailRoadName,GuardrailStartRoad,GuardrailEndRoad ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllGuardrailFaultCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,GuardrailProvince as Province,GuardrailCity as City,GuardrailArea as Area," +
                "GuardrailRoadName as RoadName,GuardrailStartRoad as StartRoad,GuardrailEndRoad as EndRoad,SUM(GuardrailMoney) as Money\n" +
                " FROM tb_guardrail as a,tb_guardrail_fault e WHERE a.GuardrailId=e.GuardrailId and a.GuardrailType in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and GuardrailProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and GuardrailCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and GuardrailArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and GuardrailRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and GuardrailStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and GuardrailEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and GuardrailOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and GuardrailMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.GuardrailTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY GuardrailProvince,GuardrailCity,GuardrailArea,GuardrailRoadName,GuardrailStartRoad,GuardrailEndRoad ";
        Query query = em.createNativeQuery(sql);
        // query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllRoadLineFaultDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,RoadLineProvince as Province,RoadLineCity as City,RoadLineArea as Area," +
                "RoadLineRoadName as RoadName,RoadLineStartRoad as StartRoad,RoadLineEndRoad as EndRoad,SUM(RoadLineMoney) as Money\n" +
                "FROM tb_road_line as a,tb_line_fault e WHERE a.RoadLineId=e.RoadLineId and a.RoadLineType in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and RoadLineProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and RoadLineCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and RoadLineArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and RoadLineRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and RoadLineStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and RoadLineEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and RoadLineOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and RoadLineMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.RoadLineTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY RoadLineProvince,RoadLineCity,RoadLineArea,RoadLineRoadName,RoadLineStartRoad,RoadLineEndRoad ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllRoadLineFaultCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,RoadLineProvince as Province,RoadLineCity as City,RoadLineArea as Area," +
                "RoadLineRoadName as RoadName,RoadLineStartRoad as StartRoad,RoadLineEndRoad as EndRoad,SUM(RoadLineMoney) as Money\n" +
                "FROM tb_road_line as a,tb_line_fault e WHERE a.RoadLineId=e.RoadLineId and a.RoadLineType in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and RoadLineProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and RoadLineCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and RoadLineArea=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and RoadLineRoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and RoadLineStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and RoadLineEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and RoadLineOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and RoadLineMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.RoadLineTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY RoadLineProvince,RoadLineCity,RoadLineArea,RoadLineRoadName,RoadLineStartRoad,RoadLineEndRoad ";
        Query query = em.createNativeQuery(sql);
        // query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllSignalMachineFaultDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,SignalMachineProvince as Province,SignalMachineCity as City,SignalMachineArea as Area," +
                "SignalMachineStartRoad as StartRoad,SignalMachineEndRoad as EndRoad,SUM(MachineMoney) as Money\n" +
                " FROM tb_signal_machine as a,tb_machine_fault e WHERE a.SignalMachineId=e.SignalMachineId and a.SignalMachineType in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and SignalMachineProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SignalMachineCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SignalMachineArea=\'" + dto.getArea() + "\' ";
       /* if (!dto.getRoadName().equals("-1"))
            sql += " and SignalMachineRoadName=\'" + dto.getRoadName() + "\' ";*/
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SignalMachineStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SignalMachineEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SignalMachineOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SignalMachineMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.MachineTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY SignalMachineProvince,SignalMachineCity,SignalMachineArea,SignalMachineStartRoad,SignalMachineEndRoad ";

        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllSignalMachineFaultCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,SignalMachineProvince as Province,SignalMachineCity as City,SignalMachineArea as Area," +
                "SignalMachineStartRoad as StartRoad,SignalMachineEndRoad as EndRoad,SUM(MachineMoney) as Money\n" +
                " FROM tb_signal_machine as a,tb_machine_fault e WHERE a.SignalMachineId=e.SignalMachineId and a.SignalMachineType in (" + dto.getSecondType() + ")";
        if (!dto.getProvince().equals("-1"))
            sql += " and SignalMachineProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SignalMachineCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SignalMachineArea=\'" + dto.getArea() + "\' ";
       /* if (!dto.getRoadName().equals("-1"))
            sql += " and SignalMachineRoadName=\'" + dto.getRoadName() + "\' ";*/
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SignalMachineStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SignalMachineEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SignalMachineOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SignalMachineMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.MachineTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY SignalMachineProvince,SignalMachineCity,SignalMachineArea,SignalMachineStartRoad,SignalMachineEndRoad ";

        Query query = em.createNativeQuery(sql);
        //   query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }

    public List<ColligateDto> getAllSignalLampFaultDT(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,SignalLampProvince as Province,SignalLampCity as City,SignalLampArea as Area," +
                "SignalLampStartRoad as StartRoad,SignalLampEndRoad as EndRoad,SUM(LampMoney) as Money\n" +
                " FROM tb_signal_lamp as a,tb_lamp_fault e WHERE a.SignalLampId=e.SignalLampId and a.SignalLampType like \'%" + dto.getSecondType() + "%\'";
        if (!dto.getProvince().equals("-1"))
            sql += " and SignalLampProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SignalLampCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SignalLampArea=\'" + dto.getArea() + "\' ";
        /*if (!dto.getRoadName().equals("-1"))
            sql += " and SignalLampRoadName=\'" + dto.getRoadName() + "\' ";*/
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SignalLampStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SignalLampEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SignalLampOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SignalLampMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.LampTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY SignalLampProvince,SignalLampCity,SignalLampArea,SignalLampStartRoad,SignalLampEndRoad ";

        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAllSignalLampFaultCount(DTRequestDto dtRequestDto, ColligateDto dto) throws Exception {
        String sql = "SELECT count(*)as count,SignalLampProvince as Province,SignalLampCity as City,SignalLampArea as Area," +
                "SignalLampStartRoad as StartRoad,SignalLampEndRoad as EndRoad,SUM(LampMoney) as Money\n" +
                " FROM tb_signal_lamp as a,tb_lamp_fault e WHERE a.SignalLampId=e.SignalLampId and a.SignalLampType like \'%" + dto.getSecondType() + "%\'";
        if (!dto.getProvince().equals("-1"))
            sql += " and SignalLampProvince=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and SignalLampCity=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and SignalLampArea=\'" + dto.getArea() + "\' ";
        /*if (!dto.getRoadName().equals("-1"))
            sql += " and SignalLampRoadName=\'" + dto.getRoadName() + "\' ";*/
        if (!dto.getStartRoad().equals("-1"))
            sql += " and SignalLampStartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and SignalLampEndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and SignalLampOrgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and SignalLampMainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and e.LampTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY SignalLampProvince,SignalLampCity,SignalLampArea,SignalLampStartRoad,SignalLampEndRoad ";

        Query query = em.createNativeQuery(sql);
        //query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();
    }


    public List<ColligateDto> getAllRoadSignFaultDT(DTRequestDto dtRequestDto, ColligateDto dto) {
        String sql = "SELECT count(*) as count,a.Province,a.City,a.Area,a.RoadName,a.StartRoad,a.EndRoad, SUM(e.RepairAmount) as Money\n" +
                "from tb_roadpole a,tb_signfault e,tb_roadsign s where s.RoadPoleId=a.RoadPoleId and e.RoadSignId=s.RoadSignId " +
                " and s.RoadSignType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and Province=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and City=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and Area=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and RoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and StartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and EndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and s.orgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and s.mainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and SignFaultTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY a.Province,a.City,a.Area,a.RoadName,a.StartRoad,a.EndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();

    }

    public Integer getAllRoadSignFaultCount(DTRequestDto dtRequestDto, ColligateDto dto) {
        String sql = "SELECT count(*) as count,a.Province,a.City,a.Area,a.RoadName,a.StartRoad,a.EndRoad, SUM(e.RepairAmount) as Money\n" +
                "from tb_roadpole a,tb_signfault e,tb_roadsign s where s.RoadPoleId=a.RoadPoleId and e.RoadSignId=s.RoadSignId " +
                " and s.RoadSignType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and Province=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and City=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and Area=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and RoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and StartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and EndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and s.orgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and s.mainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and SignFaultTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY a.Province,a.City,a.Area,a.RoadName,a.StartRoad,a.EndRoad";
        Query query = em.createNativeQuery(sql);
//        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();

    }


    public List<ColligateDto> getAllRoadSignMainFaultDT(DTRequestDto dtRequestDto, ColligateDto dto) {
        String sql = "SELECT count(*) as count,a.Province,a.City,a.Area,a.RoadName,a.StartRoad,a.EndRoad, SUM(e.RepairAmount) as Money\n" +
                "from tb_roadpole a,tb_maintenance e,tb_roadsign s where s.RoadPoleId=a.RoadPoleId and e.RoadSignId=s.RoadSignId " +
                " and s.RoadSignType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and Province=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and City=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and Area=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and RoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and StartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and EndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and s.orgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and s.mainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and MaintenanceCreateTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY a.Province,a.City,a.Area,a.RoadName,a.StartRoad,a.EndRoad";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();

    }

    public Integer getAllRoadSignFaulMaintCount(DTRequestDto dtRequestDto, ColligateDto dto) {
        String sql = "SELECT count(*) as count,a.Province,a.City,a.Area,a.RoadName,a.StartRoad,a.EndRoad, SUM(e.RepairAmount) as Money\n" +
                "from tb_roadpole a,tb_maintenance e,tb_roadsign s where s.RoadPoleId=a.RoadPoleId and e.RoadSignId=s.RoadSignId " +
                " and s.RoadSignType in (" + dto.getSecondType() + ") ";
        if (!dto.getProvince().equals("-1"))
            sql += " and Province=\'" + dto.getProvince() + "\' ";
        if (!dto.getCity().equals("-1"))
            sql += " and City=\'" + dto.getCity() + "\' ";
        if (!dto.getArea().equals("-1"))
            sql += " and Area=\'" + dto.getArea() + "\' ";
        if (!dto.getRoadName().equals("-1"))
            sql += " and RoadName=\'" + dto.getRoadName() + "\' ";
        if (!dto.getStartRoad().equals("-1"))
            sql += " and StartRoad=\'" + dto.getStartRoad() + "\' ";
        if (!dto.getEndRoad().equals("-1"))
            sql += " and EndRoad=\'" + dto.getEndRoad() + "\' ";
        if (!dto.getOrgName().equals("-1"))
            sql += " and s.orgId=" + dto.getOrgName() + " ";
        if (!dto.getMainOrgName().equals("-1"))
            sql += " and s.mainOrgId=" + dto.getMainOrgName() + " ";
        if (dto.getFaultType().length() != 0)
            sql += dto.getFaultType();
        if (dto.getTimeLimit().length() != 0) {
            String[] time = dto.getTimeLimit().split(" - ");
            sql += " and MaintenanceCreateTime BETWEEN \'" + time[0] + "\' and \'" + time[1] + "\' ";
        }
        sql += " GROUP BY a.Province,a.City,a.Area,a.RoadName,a.StartRoad,a.EndRoad";
        Query query = em.createNativeQuery(sql);
        // query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(ColligateDto.class));
        return query.getResultList().size();

    }
}
